package chemtools_logica.BDL.SFBean;

import chemtools_logica.Entidades.Ubigeo;

import chemtools_logica.BDL.IL.BDL_SFUbigeoLocal;
import chemtools_logica.BDL.IR.BDL_SFUbigeoRemote;

import java.util.ArrayList;
import java.util.List;

import javax.annotation.Resource;

import javax.ejb.SessionContext;
import javax.ejb.Stateless;

import javax.ejb.TransactionAttribute;
import javax.ejb.TransactionAttributeType;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

@Stateless(name = "BDL_SFUbigeo", mappedName = "map-BDL_SFUbigeo")
public class BDL_SFUbigeoBean implements BDL_SFUbigeoRemote, BDL_SFUbigeoLocal {
    @Resource
    SessionContext sessionContext;
    @PersistenceContext(unitName = "Chemtools_Logica")
    private EntityManager em;

    public BDL_SFUbigeoBean() {
    }

    public Ubigeo persistUbigeo(Ubigeo ubigeo) {
        em.persist(ubigeo);
        return ubigeo;
    }

    public Ubigeo mergeUbigeo(Ubigeo ubigeo) {
        return em.merge(ubigeo);
    }

    public void removeUbigeo(Ubigeo ubigeo) {
        ubigeo = em.find(Ubigeo.class, ubigeo.getIdUbigeo());
        em.remove(ubigeo);
    }

    /** <code>select o from Ubigeo o</code> */
    @TransactionAttribute(TransactionAttributeType.NOT_SUPPORTED)
    public List<Ubigeo> getUbigeoFindAll() {
        return em.createNamedQuery("Ubigeo.findAll", Ubigeo.class).getResultList();
    }

    public List<Ubigeo> getAllDepartamento() {
        List<Ubigeo> departamentos = new ArrayList<Ubigeo>();
        String ejbQuery = "SELECT u " + "FROM Ubigeo u " + "WHERE u.cod_prov = '00' " + "AND u.cod_dist = '00' ";
        try {
            Query query = em.createQuery(ejbQuery);
            departamentos = query.getResultList();
        } catch (Exception e) {
            departamentos = null;
        }
        return departamentos;
    }

    public List<Ubigeo> getProvinciaByDepartamento(String idDepartamento) {
        List<Ubigeo> provincias = new ArrayList<Ubigeo>();
        String ejbQuery =
            "SELECT u " + "FROM Ubigeo u " + "WHERE u.cod_dept = :cod_dept " + "AND u.cod_prov <> '00' " +
            "AND u.cod_dist = '00'";
        try {
            Query query = em.createQuery(ejbQuery);
            query.setParameter("cod_dept", idDepartamento);
            provincias = query.getResultList();
        } catch (Exception e) {
            provincias = null;
        }
        return provincias;
    }

    public List<Ubigeo> getDistritosByProvincia(String idProvincia) {
        List<Ubigeo> provincias = new ArrayList<Ubigeo>();
        String ejbQuery =
            "SELECT u " + "         FROM Ubigeo u " + "         WHERE u.cod_prov = :cod_prov " +
            "         AND u.cod_dist <> '00'";
        try {
            Query query = em.createQuery(ejbQuery);
            query.setParameter("cod_prov", idProvincia);
            provincias = query.getResultList();
        } catch (Exception e) {
            provincias = null;
        }
        return provincias;
    }

    public List<Ubigeo> getUbigeo(String Departamento, String Provincia, String Distrito) {
        String ejbQuery =
            "SELECT u " +
            "FROM Ubigeo u "+
            "WHERE 1=1 ";
            if(Departamento!=null){
            ejbQuery+="AND u.cod_dept = '" + Departamento +"' ";
            }
            if(Provincia!=null){
            ejbQuery+="AND u.cod_prov = '" + Provincia +"' "; 
            }
            if(Distrito!=null){
            ejbQuery+="AND u.cod_dist = '"+ Distrito+"'";
            }
            System.out.print(ejbQuery);
        return em.createQuery(ejbQuery,Ubigeo.class).getResultList();
    }
}
